using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Data.Common;

using FreeFlow;

namespace FreeFlow.Administration
{
  /// <summary>
  /// Available types of database (SQL Server, Oracle etc)
  /// </summary>
  public enum DatabaseType
  {
    /// <summary>The database is not recognised</summary>
    Unknown,
    /// <summary>The database is a SQL Server database</summary>
    [EnumDescription("SQL Server")]
    SqlServer,
    /// <summary>The database is an Oracle database</summary>
    Oracle
  }

  /// <summary>Enumerated type that defines which version of the schema the database is using</summary>
  public enum SchemaVersion
  {
    /// <summary>This is a version 6.5 database</summary>
    [EnumDescription("Version 6.5")]
    Version65,
    /// <summary>This is a version 6.6 database</summary>
    [EnumDescription("Version 6.6")]
    Version66,
    /// <summary>This is a version 7.0 database</summary>
    [EnumDescription("Version 7.0")]
    Version70,
    /// <summary>This is a version 7.0 SR1 database</summary>
    [EnumDescription("Version 7.0 SR1")]
    Version701,
    /// <summary>This is a version 7.5 database</summary>
    [EnumDescription("Version 7.5")]
    Version75,
    /// <summary>This is a version 7.6 database</summary>
    [EnumDescription("Version 7.6")]
    Version76
  }

	/// <summary>
	/// Wrapper class around the database server.
	/// </summary>
  public sealed class Server : IDisposable, IUpdatable, IRefreshable, IGetAttachment
  {
    private SchemaVersion schemaVersion;
    /// <summary>Gets the version of the schema the database is using</summary>
    public SchemaVersion SchemaVersion
    {
      get 
      {
        return schemaVersion;
      }
    }

    private IDbConnection connection;
    /// <summary>
    /// Creates a new <see cref="Server"/> instance.
    /// </summary>
    public Server()
    {
    }

    #region IDisposable Members

    /// <summary>
    /// Disposes this instance.
    /// </summary>
    public void Dispose()
    {
      if (connection != null)
        connection.Dispose();
    }

    #endregion
    #region connect
    /// <summary>
    /// Attempts to connect to the database server using ODBC.
    /// </summary>
    /// <param name="userName">Name of the user.</param>
    /// <param name="password">The password.</param>
    /// <param name="dsn">The datasource name.</param>
    public void Connect(string userName, string password, string dsn)
    {
      connection = new OdbcConnection();
      string connectionString = 
        string.Format("UID={0};PWD={1};DSN={2}", userName, password, dsn);
      InternalConnect(connectionString);
      datasource = ((OdbcConnection)connection).DataSource;
      this.dsn = dsn;
    }

    /// <summary>
    /// Attempts to connect to the database server using an OLEDB connection string.
    /// </summary>
    /// <param name="connectionString">Connection string.</param>
    public void Connect(string connectionString)
    {
      connection = new OleDbConnection();
      InternalConnect(connectionString);
      datasource = ((OleDbConnection)connection).DataSource;
    }

    private void InternalConnect(string connectionString)
    {
      if (connection.ConnectionString != connectionString)
      {
        connection.Close();
        connection.ConnectionString = connectionString;
      }
      EnsureConnectionOpen();

      // see if this is an e-Work database
      IDataReader reader;
      try
      {
        reader = ExecuteReader("SELECT * FROM eServer");
      }
      catch (DbException e)
      {
        throw new FreeFlowException("This database does not appear to be a valid Metastorm database", e);
      }
  
      // read server settings
      try
      {
        reader.Read();
        
        schemaVersion = SchemaVersion.Version65;

        DataTable schema = reader.GetSchemaTable();
        for (int i = 0; i < schema.Rows.Count; i++)
        { 
          if (schema.Rows[i]["ColumnName"].ToString().ToUpper() == "EEVENTHANDLERFUNCTION")
          {
            if (schemaVersion < SchemaVersion.Version75)
              schemaVersion = SchemaVersion.Version75;
          }
          if (schema.Rows[i]["ColumnName"].ToString().ToUpper() == "EDELETEDELETIONALERTS")
          {
            if (schemaVersion < SchemaVersion.Version701)
              schemaVersion = SchemaVersion.Version701;
          }
          else if (schema.Rows[i]["ColumnName"].ToString().ToUpper() == "ENOTIFICATION")
          {
            if (schemaVersion < SchemaVersion.Version70)
              schemaVersion = SchemaVersion.Version70;
          }
          else if (schema.Rows[i]["ColumnName"].ToString().ToUpper() == "EKEEPALIVEINTERVAL")
          {
            if (schemaVersion < SchemaVersion.Version66)
              schemaVersion = SchemaVersion.Version66;
          }
        }

        name = reader["eServerName"].ToString();
        string dbType = reader["eDatabaseType"].ToString();
        if (dbType == "Oracle")
          databaseType = DatabaseType.Oracle;
        else if ((dbType == "Microsoft SQL Server") || (dbType == "MS SQL Server"))
          databaseType = DatabaseType.SqlServer;
        else
        {
          Debug.Assert(false, "Unexpected database type - " + dbType);
          databaseType = DatabaseType.Unknown;
        }

        folderLockTimeout = Convert.ToInt32(reader["eFolderLockTimeout"]);
        sessionTimeout = Convert.ToInt32(reader["eSessionTimeout"]);
        guestUserName = reader["eGuestUserName"].ToString();
        if (schemaVersion >= SchemaVersion.Version66)
          keepAliveInterval = Convert.ToInt32(reader["eKeepAliveInterval"]);
        if (schemaVersion >= SchemaVersion.Version70)
          notificationFormula = reader["eNotification"].ToString();
        if (schemaVersion >= SchemaVersion.Version701)
        {
          enableDeletionAlerts = !Convert.ToBoolean(reader["eDeleteDeletionAlerts"]);
          enableAlertGeneration = Convert.ToBoolean(reader["eDoAlertGeneration"]);
        }
        if (schemaVersion >= SchemaVersion.Version75)
        {
          eventHandlerFunction = reader["eEventHandlerFunction"].ToString();
        }
      }
      finally
      {
        if (reader != null)
          reader.Dispose();
      }

      // see if version 7.6
      if (schemaVersion == SchemaVersion.Version75)
      {
        IDataReader workflowReader = null;
        try
        {
          workflowReader = ExecuteReader("SELECT * FROM eMSWorkflow");
          schemaVersion = SchemaVersion.Version76;
        }
        catch (DbException)
        {
        }
        finally
        {
          if (workflowReader != null)
            workflowReader.Dispose();
        }
      }
    }

    private void EnsureConnectionOpen()
    {
      if (connection.State != ConnectionState.Open)
        connection.Open();
    }

    /// <summary>
    /// Gets a value indicating whether the database has been logged in to.
    /// </summary>
    /// <value><c>true</c> if the database has been logged in to; otherwise, <c>false</c>.</value>
    public bool LoggedIn
    {
      get
      {
        return (connection != null) && (connection.State == ConnectionState.Open);
      }
    }

    #endregion

    /// <summary>
    /// Gets the name of the database that the instance is connected to.
    /// </summary>
    public string Database
    {
      get
      {
        if (connection != null)
          return connection.Database;
        else
          return "";
      }
    }

    private string dsn;
    /// <summary>
    /// If the user has logged in via an ODSB DSN, gets the name of the DSN.
    /// </summary>
    public string Dsn
    {
      get
      {
        return dsn;
      }
    }

    private string datasource;
    /// <summary>
    /// Gets the name of the datasource that the instance is connected to.
    /// </summary>
    public string Datasource
    {
      get
      {
        return datasource;
      }
    }

    private int queryTimeout;
    /// <summary>
    /// Gets or sets the wait time in seconds before terminating the attempt to execute a command and generating an error.  
    /// </summary>
    public int QueryTimeout
    {
      get
      {
        return queryTimeout;
      }
      set
      {
        queryTimeout = value;
      }
    }

    #region SQL execution
    
    /// <summary>
    /// Executes the specified SQL and returns a data reader.
    /// </summary>
    /// <param name="sql">The SQL to execute.</param>
    public IDataReader ExecuteReader(string sql)
    {
      return ExecuteReader(null, sql);
    }
    
    /// <summary>
    /// Executes the specified SQL and returns a data reader.
    /// </summary>
    /// <param name="transaction">The transaction.</param>
    /// <param name="sql">The SQL to execute.</param>
    public IDataReader ExecuteReader(IDbTransaction transaction, string sql)
    {
      EnsureConnectionOpen();
      IDbCommand command = CreateCommand();
      command.Transaction = transaction;
      command.CommandText = sql;
      TriggerSqlExecutingEvent(sql);
      IDataReader reader = command.ExecuteReader();
      TriggerSqlExecutedEvent(sql);
      return reader;
    }

    /// <summary>
    /// Executes the SQL and generates a data table.
    /// </summary>
    /// <param name="sql">The SQL used to generate the data table.</param>
    public DataTable ExecuteDataTable(string sql)
    {
      TriggerSqlExecutingEvent(sql);
      DataSet dataSet = new DataSet();
      IDbDataAdapter adapter;

      if (connection is OleDbConnection)
      {
        adapter = new OleDbDataAdapter(sql, (OleDbConnection)connection);
      }
      else
        adapter = new OdbcDataAdapter(sql, (OdbcConnection)connection);

      adapter.SelectCommand.CommandTimeout = queryTimeout;
      adapter.Fill(dataSet);
      TriggerSqlExecutedEvent(sql);

      if (dataSet.Tables.Count > 0)
        return dataSet.Tables[0];
      else
        return null;
    }

    /// <summary>
    /// Executes a SQL command that doesn't return a result set.
    /// </summary>
    /// <param name="sql">The SQL to execute.</param>
    /// <returns>The number of rows affected.</returns>
    public int ExecuteNonQuery(string sql)
    {
      return ExecuteNonQuery(null, sql);
    }

    /// <summary>
    /// Executes a SQL command that doesn't return a result set.
    /// </summary>
    /// <param name="transaction">The transaction.</param>
    /// <param name="sql">The SQL to execute.</param>
    /// <returns>The number of rows affected.</returns>
    public int ExecuteNonQuery(IDbTransaction transaction, string sql)
    {
      EnsureConnectionOpen();
      using (IDbCommand command = CreateCommand())
      {
        command.Transaction = transaction;
        command.CommandText = sql;
        TriggerSqlExecutingEvent(sql);
        int rowsAffected = command.ExecuteNonQuery();
        TriggerSqlExecutedEvent(sql);
        return rowsAffected;
      }
    }

    /// <summary>
    /// Executes the query, and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
    /// </summary>
    /// <param name="sql">The SQL to execute.</param>
    /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
    public object ExecuteScalar(string sql)
    {
      return ExecuteScalar(null, sql);
    }

    /// <summary>
    /// Executes the query, and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
    /// </summary>
    /// <param name="transaction">The transaction.</param>
    /// <param name="sql">The SQL to execute.</param>
    /// <returns>
    /// The first column of the first row in the result set, or a null reference if the result set is empty.
    /// </returns>
    public object ExecuteScalar(IDbTransaction transaction, string sql)
    {
      EnsureConnectionOpen();
      IDbCommand command = CreateCommand();
      command.Transaction = transaction;
      command.CommandText = sql;
      TriggerSqlExecutingEvent(sql);
      object ret = command.ExecuteScalar();
      TriggerSqlExecutedEvent(sql);
      return ret;
    }

    /// <summary>
    /// Creates and returns a Command object associated with the connection.
    /// </summary>
    public IDbCommand CreateCommand()
    {
      IDbCommand command = connection.CreateCommand();
      command.CommandTimeout = queryTimeout;
      return command;
    }

    /// <summary>
    /// Creates a parameter for use in parametrised queries.
    /// </summary>
    public IDataParameter CreateParameter()
    {
      if (connection is OleDbConnection)
        return new OleDbParameter();
      else
        return new OdbcParameter();
    }

    private void TriggerSqlExecutingEvent(string sql)
    {
      if (SqlExecuting != null)
        SqlExecuting(this, new SqlExecutingEventArgs(sql));
    }

    /// <summary>
    /// Event handler triggered when SQL is about to be executed against the database
    /// </summary>
    public event EventHandler<SqlExecutingEventArgs> SqlExecuting;

    private void TriggerSqlExecutedEvent(string sql)
    {
      if (SqlExecuted != null)
        SqlExecuted(this, new SqlExecutingEventArgs(sql));
    }

    /// <summary>
    /// Event handler triggered when SQL has been executed against the database
    /// </summary>
    public event EventHandler<SqlExecutingEventArgs> SqlExecuted;
    #endregion

    /// <summary>
    /// Begins a transaction.
    /// </summary>
    /// <returns>The transaction object</returns>
    public IDbTransaction BeginTransaction()
    {
      EnsureConnectionOpen();
      return connection.BeginTransaction(); 
    }

    private ProcedureCollection procedures;
    /// <summary>
    /// Gets the published procedures.
    /// </summary>
    public ProcedureCollection Procedures
    {
      get
      {
        if (procedures == null)
          procedures = new ProcedureCollection(this);
        return procedures;
      }
    }

    private LibraryCollection libraries;
    /// <summary>
    /// Gets the published libraries.
    /// </summary>
    /// <value>The libraries.</value>
    public LibraryCollection Libraries
    {
      get
      {
        if (libraries == null)
          libraries = new LibraryCollection(this);
        return libraries;
      }
    }

    private UserCollection users;
    /// <summary>
    /// Gets the list of registered users.
    /// </summary>
    /// <value>The users.</value>
    public UserCollection Users
    {
      get
      {
        if (users == null)
          users = new UserCollection(this);
        return users;
      }
    }

    private RoleCollection roles;
    /// <summary>
    /// Gets all the available roles in the database.
    /// </summary>
    public RoleCollection Roles
    {
      get
      {
        if (roles == null)
          roles = new RoleCollection(this);
        return roles;
      }
    }

    private MapCollection maps;
    /// <summary>
    /// Gets all the maps available in this database.
    /// </summary>
    /// <value>The maps.</value>
    public MapCollection Maps
    {
      get
      {
        if (maps == null)
          maps = new MapCollection(this);
        return maps;
      }
    }

    /// <summary>
    /// Retrieves the latest procedures and libraries from the server.
    /// </summary>
    /// <param name="folderName">Name of the folder to retrieve the procedures to.</param>
    public void RetrieveLatestProcedures(string folderName)
    {
      for (int i = 0; i < Procedures.Count; i++)
      {
        Version version = Procedures[i].Versions.LatestVersion;
        string fileName = Path.Combine(folderName, Procedures[i].Name + "." + version.Type);
        version.SaveToFile(fileName);
      }

      for (int i = 0; i < Libraries.Count; i++)
      {
        Version version = Libraries[i].Versions.LatestVersion;
        string fileName = Path.Combine(folderName, Libraries[i].Name + "." + version.Type);
        version.SaveToFile(fileName);
      }
    }

    private DesignerLog designerLog;
    /// <summary>
    /// Gets the errors and publishing details from the designer log.
    /// </summary>
    public DesignerLog DesignerLog
    {
      get
      {
        if (designerLog == null)
          designerLog = new DesignerLog(this, DesignerLogSources.EngineError | DesignerLogSources.DesignerPublishing);
        return designerLog;
      }
    }

    private DesignerLog authenticationFailures;
    /// <summary>
    /// Gets any authentication failures.
    /// </summary>
    public DesignerLog AuthenticationFailures
    {
      get
      {
        if (authenticationFailures == null)
          authenticationFailures = new DesignerLog(this, DesignerLogSources.AuthenticationFailure);
        return authenticationFailures;
      }
    }

    private ReportCollection reports;
    /// <summary>
    /// Gets the reports.
    /// </summary>
    public ReportCollection Reports
    {
      get
      {
        if (reports == null)
          reports = new ReportCollection(this);
        return reports;
      }
    }

    /// <summary>
    /// Applies any changes made to this server.
    /// </summary>
    public void ApplyChanges()
    {
      string sql = string.Format(
        "UPDATE eServer SET eServerName = '{0}', eFolderLockTimeout = {1}, eSessionTimeout = {2}, eGuestUserName = '{3}'", 
        DatabaseAware.SqlEscape(name), DatabaseAware.SqlEscape(folderLockTimeout.ToString()), 
        DatabaseAware.SqlEscape(sessionTimeout.ToString()), DatabaseAware.SqlEscape(guestUserName));
      if (schemaVersion >= SchemaVersion.Version66)
        sql = sql + string.Format(", eKeepAliveInterval = {0}", keepAliveInterval);
      if (schemaVersion >= SchemaVersion.Version70)
        sql = sql + string.Format(", eNotification = '{0}'", DatabaseAware.SqlEscape(notificationFormula));
      if (schemaVersion >= SchemaVersion.Version701)
      {
        sql = sql + string.Format(", eDeleteDeletionAlerts = {0}, eDoAlertGeneration = {1}", 
          Convert.ToInt32(!enableDeletionAlerts), Convert.ToInt32(enableAlertGeneration));
      }
      if (schemaVersion >= SchemaVersion.Version75)
      {
        sql = sql + string.Format(", eEventHandlerFunction='{0}'", DatabaseAware.SqlEscape(eventHandlerFunction));
      }
      ExecuteNonQuery(sql);
    }

    #region properties

    private string name;
    /// <summary>
    /// Gets or sets the name of this server.
    /// </summary>
    public string Name
    {
      get
      {
        return name;
      }
      set
      {
        name = value;
      }
    }

    private DatabaseType databaseType;
    /// <summary>
    /// Gets the type of the database.
    /// </summary>
    public DatabaseType DatabaseType
    {
      get
      {
        return databaseType;
      }
    }

    private int folderLockTimeout;
    /// <summary>
    /// Gets or sets the folder lock timeout in minutes.
    /// </summary>
    public int FolderLockTimeout
    {
      get
      {
        return folderLockTimeout;
      }
      set
      {
        folderLockTimeout = value;
      }
    }

    private int sessionTimeout;
    /// <summary>
    /// Gets or sets the session timeout in minutes.
    /// </summary>
    public int SessionTimeout
    {
      get
      {
        return sessionTimeout;
      }
      set
      {
        sessionTimeout = value;
      }
    }

    private int keepAliveInterval;
    /// <summary>
    /// Gets or sets the engine keep alive interval in minutes (version 6.6 and later).
    /// </summary>
    public int KeepAliveInterval
    {
      get
      {
        return keepAliveInterval;
      }
      set
      {
        keepAliveInterval = value;
      }
    }

    private string guestUserName;
    /// <summary>
    /// Gets or sets the name of the guest user.
    /// </summary>
    public string GuestUserName
    {
      get
      {
        return guestUserName;
      }
      set
      {
        guestUserName = value;
      }
    }

    private string notificationFormula;
    /// <summary>
    /// Gets or sets the formula that will be evaluated each time an action is performed (version 7 and later).
    /// </summary>
    /// <value>The notification formula.</value>
    public string NotificationFormula
    {
      get
      {
        return notificationFormula;
      }
      set
      {
        notificationFormula = value;
      }
    }


    private bool enableDeletionAlerts;
    /// <summary>
    /// Gets or sets a value indicating whether deletion alerts will be generated (version 7.01 and later).
    /// </summary>
    /// <value>
    /// 	<c>true</c> if deletion alerts will be generated; otherwise, <c>false</c>.
    /// </value>
    public bool EnableDeletionAlerts
    {
      get
      {
        return enableDeletionAlerts;
      }
      set
      {
        enableDeletionAlerts = value;
      }
    }

    private bool enableAlertGeneration;
    /// <summary>
    /// Gets or sets a value indicating whether alerts will be generated (version 7.01 and later).
    /// </summary>
    /// <value>
    /// 	<c>true</c> if alerts will be generated; otherwise, <c>false</c>.
    /// </value>
    public bool EnableAlertGeneration
    {
      get
      {
        return enableAlertGeneration;
      }
      set
      {
        enableAlertGeneration = value;
      }
    }

    private string eventHandlerFunction;
    /// <summary>
    /// Gets or sets the event handler function call that is used to process events.
    /// </summary>
    public string EventHandlerFunction
    {
      get
      {
        return eventHandlerFunction;
      }
      set
      {
        eventHandlerFunction = value;
      }
    }

    #endregion

    private const int guidLength = 38 * 2;
    /// <summary>
    /// Gets an attachment from the database.
    /// </summary>
    /// <param name="type">The type of attachment.</param>
    /// <param name="file">The name of the file.</param>
    /// <param name="owner">The owner (map name, folder ID or procedure name).</param>
    public byte[] GetAttachment(AttachmentType type, string file, string owner)
    {
      using (IDataReader reader = ExecuteReader(
               string.Format("SELECT eContents FROM eAttachment WHERE eKey = '{0}\t{1}\t{2}'", 
               (int)type, DatabaseAware.SqlEscape(owner), DatabaseAware.SqlEscape(file))))
      {
        if (reader.Read())
        {
          if (type == AttachmentType.Folder)
          {
            // remove GUID
            if (reader[0] == System.DBNull.Value)
              return new byte[0];

            byte[] data = (byte[])reader[0];
            byte[] truncatedData;
            if (data[data.Length - 2] == 0)
              truncatedData = new byte[data.Length - guidLength - 2];
            else
              truncatedData = new byte[data.Length - guidLength];
            Array.Copy(data, guidLength, truncatedData, 0, truncatedData.Length);

            // base64 unencode
            string truncatedString = Encoding.Unicode.GetString(truncatedData);
            return Convert.FromBase64String(truncatedString);
          }
          else
            return (byte[])reader[0];
        }
      }

      return null;
    }

    /// <summary>
    /// Writes an attachment to the database. Folder attachments are not currently supported.
    /// </summary>
    /// <param name="type">The type of attachment.</param>
    /// <param name="file">The name of the file.</param>
    /// <param name="owner">The owner (map name, folder ID or procedure name).</param>
    /// <param name="data">The attachment data.</param>
    public void SetAttachment(AttachmentType type, string file, string owner, byte[] data)
    {
      // prefix folder attachments with GUID
      if (type == AttachmentType.Folder)
      {
        string encoded = "{501F235F-75F0-4964-9A79-AE6350BB918C}" + Convert.ToBase64String(data);
        data = Encoding.Unicode.GetBytes(encoded);
      }

      using (IDbTransaction transaction = BeginTransaction())
      {
        ExecuteNonQuery(transaction, string.Format("DELETE FROM eAttachment WHERE eKey='{0}\t{1}\t{2}'",
          (int)type, DatabaseAware.SqlEscape(owner), DatabaseAware.SqlEscape(file)));

        // Construct INSERT Command
        IDbCommand updateScript = CreateCommand();
        updateScript.Transaction = transaction;
        updateScript.CommandText = 
          string.Format("INSERT INTO eAttachment (eKey, eContents, eSize) VALUES ('{0}\t{1}\t{2}', ?, {3})", 
          (int)type, DatabaseAware.SqlEscape(owner), DatabaseAware.SqlEscape(file), data.Length);
        IDataParameter parameter = CreateParameter();
        parameter.Value = data;
        parameter.DbType = DbType.Binary;
        parameter.Direction = ParameterDirection.Input;
        updateScript.Parameters.Add(parameter);
        updateScript.ExecuteNonQuery();
      
        transaction.Commit();
      }
    }

    /// <summary>
    /// Deletes the specified attachment from the database.
    /// </summary>
    /// <param name="type">The type.</param>
    /// <param name="file">The file.</param>
    /// <param name="owner">The owner.</param>
    public void DeleteAttachment(AttachmentType type, string file, string owner)
    {
      ExecuteNonQuery(string.Format("DELETE FROM eAttachment WHERE eKey='{0}\t{1}\t{2}'", 
        (int)type, DatabaseAware.SqlEscape(owner), DatabaseAware.SqlEscape(file)));
    }

    /// <summary>
    /// Saves the specified attachment to a file on disk.
    /// </summary>
    /// <param name="type">The type of attachment.</param>
    /// <param name="file">The attachment filename.</param>
    /// <param name="owner">The owner of the attachment (folder ID for folder attachments, map name for map attachments, procedure name for procedure attachments).</param>
    /// <param name="fileName">Name of the file to save to.</param>
    public void SaveAttachmentToFile(AttachmentType type, string file, string owner, string fileName)
    {
      byte[] data = GetAttachment(type, file, owner);
      if (data == null)
        throw new FreeFlowException("Attachment has no data, it may have been deleted");
      using (FileStream writer = new FileStream(fileName, FileMode.Create))
      {
        writer.Write(data, 0, data.Length);
      }
    }

    private SessionCollection sessions;
    /// <summary>
    /// Gets the active sessions for this database.
    /// </summary>
    /// <value>The sessions.</value>
    public SessionCollection Sessions
    {
      get
      {
        if (sessions == null)
          sessions = new SessionCollection(this);
        return sessions;
      }
    }

    private AttachmentCollection attachments;
    /// <summary>
    /// Gets the service attachments for this server.
    /// </summary>
    public AttachmentCollection Attachments
    {
      get
      {
        if (attachments == null)
          attachments = new AttachmentCollection(this, AttachmentType.Service);
        return attachments;
      }
    }

    /// <summary>
    /// Deletes any deletion alerts older than the specified number of days.
    /// </summary>
    /// <param name="olderThanDays">The number of days that deletion alerts should be older than to be deleted.</param>
    /// <returns>The number of records deleted</returns>
    public int DeleteDeletionAlerts(int olderThanDays)
    {
      return ExecuteNonQuery(string.Format("DELETE FROM eAlert WHERE eAlertType= '~' AND eAlertTime < " + GetDate + " - {0}", olderThanDays));
    }

    /// <summary>
    /// Deletes any alerts without an associated folder.
    /// </summary>
    /// <returns>The number of records deleted</returns>
    public int DeleteAlertsWithoutFolder()
    {
      return ExecuteNonQuery("DELETE FROM eAlert WHERE eFolderID NOT IN (SELECT eFolderID FROM eFolder)");
    }

    /// <summary>
    /// Deletes any events without an associated folder.
    /// </summary>
    /// <returns>The number of records deleted</returns>
    public int DeleteEventsWithoutFolder()
    {
      return ExecuteNonQuery("DELETE FROM eEvent WHERE eFolderID NOT IN (SELECT eFolderID FROM eFolder)");
    }

    /// <summary>
    /// Updates the groups for blank forms and administration forms, so that any set to the default 'Forms' will
    /// have their group set to be the procedure name.
    /// </summary>
    public void UpdateBlankFormsGroups()
    {
      ExecuteNonQuery("UPDATE eStart SET eGroupName=eProcedureName WHERE eGroupName = 'Forms' ");
    }

    /// <summary>
    /// Gets a Folder instance for the specified folder ID.
    /// </summary>
    /// <param name="folderId">The folder id.</param>
    /// <returns>The folder, if it exists, null otherwise</returns>
    public Folder GetFolder(string folderId)
    {
      Folder folder = null;
      
      if (FolderExists(folderId))
        folder = new Folder(this, folderId);

      return folder;
    }
    
    /// <summary>
    /// Indicates whether the folder with the specified folder ID exists.
    /// </summary>
    /// <param name="folderId">The folder id.</param>
    public bool FolderExists(string folderId)
    {
      using (IDataReader reader = ExecuteReader(
               string.Format("SELECT * FROM eFolder WHERE eFolderID = '{0}'", folderId)))
      {
        return reader.Read();
      }
    }

    /// <summary>
    /// Removes all stages from all maps that are no longer defined in the latest published version of the procedure and have no folders still at that stage.
    /// </summary>
    /// <returns>The number of stages removed</returns>
    public int PurgeStages()
    {
      // TODO - do in one piece of SQL?
      int count = 0;
      for (int i = 0; i < Maps.Count; i++)
      {
        count = count + Maps[i].PurgeStages();
      }
      return count;
    }

    /// <summary>
    /// For version 6.6 and later database, gets the number of raised flags pending processing. If this value is too high, 
    /// it may indicate a problem with the eRaisedFlag table 
    /// </summary>
    public int RaisedFlagsPending
    {
      get
      {
        if (SchemaVersion >= SchemaVersion.Version66)
          return Convert.ToInt32(ExecuteScalar("SELECT COUNT(*) FROM eRaisedFlag"));
        else
          return 0;
      }
    }

    private AuthenticationScriptCollection authenticationScripts;
    /// <summary>
    /// Gets the authentication scripts that have been installed on this database.
    /// </summary>
    public AuthenticationScriptCollection AuthenticationScripts
    {
      get
      {
        if (authenticationScripts == null)
          authenticationScripts = new AuthenticationScriptCollection(this);
        return authenticationScripts;
      }
    }
 
    private TableCollection tables;
    /// <summary>
    /// Gets the tables available in this database.
    /// </summary>
    public TableCollection Tables
    {
      get
      {
        if (tables == null)
          tables = new TableCollection(this);
        return tables;
      }
    }

    private WorkflowCollection workflows;
    /// <summary>
    /// Gets the Windows Workflows available in this database.
    /// </summary>
    public WorkflowCollection Workflows
    {
      get 
      {
        if (workflows == null)
          workflows = new WorkflowCollection(this);
        return workflows; 
      }
    }

    private WorkflowInstanceCollection workflowInstances;
    /// <summary>
    /// Gets the Windows Workflow instances created in this database.
    /// </summary>
    public WorkflowInstanceCollection WorkflowInstances
    {
      get
      {
        if (workflowInstances == null)
          workflowInstances = new WorkflowInstanceCollection(this);
        return workflowInstances;
      }
    }

    internal string WithNoLock
    {
      get
      {
        switch (databaseType)
        {
          case DatabaseType.SqlServer: return "WITH (NOLOCK)";
          default: return "";
        }
      }
    }

    internal string GetDate
    {
      get
      {
        switch (databaseType)
        {
          case DatabaseType.SqlServer: return "GETDATE()";
          case DatabaseType.Oracle: return "SYSDATE";
          default: throw new FreeFlowException("Unrecognised database - " + databaseType.ToString());
        }
      }
    }

    #region IRefreshable Members

    /// <summary>
    /// Refreshes the data for this server.
    /// </summary>
    public void Refresh()
    {
      procedures = null;
      libraries = null;
      users = null;
      roles = null;
      maps = null;
      designerLog = null;
      reports = null;
      sessions = null;
      attachments = null;
      authenticationScripts = null;
      tables = null;
      workflows = null;
      workflowInstances = null;
    }

    #endregion

    /// <summary>
    /// Searches for the specified text in formulae.
    /// </summary>
    /// <param name="text">The text to search for.</param>
    /// <returns>A DataTable containing the search results</returns>
    public DataTable Search(string text)
    {
      string sqlToExecute = string.Format(
        "SELECT eProcedureName AS \"Procedure\", '' AS Map, 'Field' AS \"Component Type\", eFormName + '\\' + eFieldName AS \"Component Name\", " +
        "'Options' AS Property, CONVERT(varchar(4000), eOptions) AS \"Formula\" FROM eField WHERE eOptions LIKE '%{0}%' UNION " +
        "SELECT eProcedureName, '', 'Field', eFormName + '\\' + eFieldName, 'On Update', CONVERT(varchar(4000), eOnUpdate) FROM eField WHERE eOnUpdate LIKE '%{0}%' UNION " +
        "SELECT eProcedureName, '', 'Field', eFormName + '\\' + eFieldName, 'Data Value', CONVERT(varchar(4000), eDataValue) FROM eField WHERE eDataValue LIKE '%{0}%' UNION " +
        "SELECT eProcedureName, M.eMapName, 'Action', eActionName, 'On Commit', CONVERT(varchar(4000), eOnCommit) FROM eAction A, eMap M WHERE A.eMapName=M.eMapName AND eOnCommit LIKE '%{0}%' UNION " +
        "SELECT eProcedureName, M.eMapName, 'Action', eActionName, 'On Invoke', CONVERT(varchar(4000), eOnInvoke) FROM eAction A, eMap M WHERE A.eMapName=M.eMapName AND eOnInvoke LIKE '%{0}%' UNION " +
        "SELECT eProcedureName, '', 'Form', eFormName, 'On Load', CONVERT(varchar(4000), eOnLoad) FROM eForm WHERE eOnLoad LIKE '%{0}%' UNION " +
        "SELECT eProcedureName, '', 'Form', eFormName, 'On Save', CONVERT(varchar(4000), eOnSave) FROM eForm WHERE eOnSave LIKE '%{0}%' UNION " +
        "SELECT eProcedureName, M.eMapName, 'Stage', eStageName, 'On Entry', CONVERT(varchar(4000), eOnEntry) FROM eStage S, eMap M WHERE S.eMapName=M.eMapName AND eOnEntry LIKE '%{0}%' UNION " +
        "SELECT eProcedureName, M.eMapName, 'Stage', eStageName, 'On Exit', CONVERT(varchar(4000), eOnExit) FROM eStage S, eMap M WHERE S.eMapName=M.eMapName AND eOnExit LIKE '%{0}%'", 
        DatabaseAware.SqlEscape(text));
      return ExecuteDataTable(sqlToExecute);
    }

    // TODO - requires .NET 3, do we want to use it yet?
    ///// <summary>
    ///// Publishes a WF workflow to the database.
    ///// </summary>
    ///// <param name="assemblyFileName">Name of the assembly file containing the workflow.</param>
    //public void PublishWorkflow(string assemblyFileName)
    //{
    //  if (SchemaVersion < SchemaVersion.Version76)
    //    throw new FreeFlowException("Windows Workflows are only supported on version 7.6 or later");

    //  // get the assembly
    //  Assembly assembly = Assembly.LoadFrom(fileName);

    //  // get the workflow type
    //  Type workflowType = null;
    //  Type[] types = assembly.GetTypes();
    //  for (int i = 0; i < types.Length; i++)
    //  {
    //    if (types[i].IsSubclassOf(typeof(SequentialWorkflowActivity)))
    //    {
    //      workflowType = types[i];
    //    }
    //  }

    //  if (workflowType == null)
    //    throw new FreeFlowException("Can't find a workflow in the assembly");

    //}
  }
}
